package com.jcloudsoft.springbootshardingsphere.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.jcloudsoft.springbootshardingsphere.domain.Student;
import com.jcloudsoft.springbootshardingsphere.domain.Teacher;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;

/**
 * (SsmUser)表数据库访问层
 *
 * @author oyc
 * @since 2020-04-28 23:21:52
 */
@Mapper
public interface TeacherMapper extends BaseMapper<Teacher> {



    //@Select("select * from TABLE user  UNION all select * from TABLE user ")

    @Select("SELECT * FROM teacher ")
    List<Teacher> getSelectSql();

    @Select("SELECT * FROM teacher UNION ALL SELECT * FROM teacher")
    List<Teacher> getUnionAllSql();


    @Select("select sum(userId) from teacher")
    Long getSumSql();

    @Select("SELECT * FROM teacher where userId=1493896663163502594")
    Teacher getOneSql();

    @Select("SELECT * FROM teacher where userId BETWEEN (1493764168258539522 AND 149376416825853100)")
    List<Teacher> getbetween();


    @Select("SELECT * FROM teacher where userId<=1493896663163502594 ")
    List<Teacher> getLess();

    @Select("SELECT * FROM teacher where userId<=1493764168073990146 and userId>=1493764168073090146 ")
    List<Teacher> getLessAndGreat();


    @Select("SELECT * FROM teacher where (userId<=1493764168073990146 and userId>=1493764168073990146) and (age>=10 and age<=50) ")
    List<Teacher> getLessAndGreat1();

    @Select("SELECT * FROM teacher where userId in (1493764168258539522 ,1493764168258500001) ")
    List<Teacher> getIn();


    @Select("select * from t1")
    List getT1();

    @Select("SELECT s.* FROM teacher t,student s WHERE t.userId=s.teacherId and t.userId=1493896663163502594")
    Student getStudent();


    @Select("SELECT s.* FROM teacher t left join student s ON t.userId=s.teacherId  WHERE  t.userId=1493764167679725570")
    List<Student> getStudentLeft();

    @Select("SELECT * FROM teacher t WHERE date_format(createTime, 'yyyy-mm-dd') = '2019-01-01';")
    List<Teacher> getDateTranslter();


    @Select("SELECT age,count(*) as num from teacher group by age having count(*)>3")
    List<HashMap> getGroupBy();

}

